package com.wanmait.myhouse.dao;
import com.wanmait.myhouse.util.DBHelper;
import com.wanmait.myhouse.vo.House;
import com.wanmait.myhouse.vo.Pager;
import com.wanmait.myhouse.vo.SellManner;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.taglibs.standard.extra.spath.Step;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class HouseDAO {

    public List<House> selectAll(){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,t_house.id as id, t_sellManner.id as sellMannerId ,house_info as houseInfo from t_house,t_sellManner  where t_house.mannerId=t_sellManner.id and sold=1 ORDER BY time DESC";
        try {
            List<Map<String, Object>> maps = queryRunner.query(sql, new MapListHandler());
            List<House> houseList = new ArrayList<>();
            for (Map<String,Object> map : maps){
                House house = new House();
                houseList.add(house);
                house.setId((Integer) map.get("id"));
                house.setCity((String) map.get("city"));
                house.setType((String) map.get("type"));
                house.setRoom((String) map.get("room"));
                house.setPrice((String) map.get("price"));
                house.setArea((String) map.get("area"));
                house.setHouseInfo((String) map.get("houseInfo"));
                house.setMannerId((Integer) map.get("mannerId"));
                house.setMediatorId((Integer) map.get("mediatorId"));
                house.setShowImg((String) map.get("showImg"));
                SellManner sellManner = new SellManner();
                sellManner.setSellManner((String) map.get("sellManner"));
                sellManner.setId((Integer) map.get("sellMannerId"));
                house.setSellManner(sellManner);
            }
            return houseList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public Integer getTotal(Integer id){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select count(*) as total from t_house where mediatorId=?";
        try {
            List<Map<String, Object>> mapList = queryRunner.query(sql, new MapListHandler(), id);
            Integer count = Integer.parseInt(mapList.get(0).get("total").toString());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public List<House> saved(House house , Pager pager){//总页数
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql;
        int start = (pager.getPageNO() - 1) * pager.getPageSize();
        if (house.getCity() != null) {
            if (house.getRoom() != null) {
                sql = "select * from t_house where city=? and room = ? and type=? order by id asc limit ?,?";
                try {
                    List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getCity(), house.getRoom(), house.getType(), start, pager.getPageSize());
                    return houseList;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            } else {
                sql = "select * from t_house where  city=?  and type =? order by id asc limit ?,?";
                try {
                    List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getCity(), house.getType(), start, pager.getPageSize());
                    return houseList;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            }
        } else {
            if (house.getRoom() != null) {//城市空
                sql = "select * from t_house where room=? and type =? order by id asc limit ?,?";
                try {
                    List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getRoom(), house.getType(), start, pager.getPageSize());
                    return houseList;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            }
            return null;
        }
    }



    public Integer getTotal(House house){//总页数
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql;
        if (house.getCity() != null) {
            if (house.getRoom() != null) {
                sql = "select count(*) as total from t_house where city=? and room = ? and type =?";
                try {
                    List<Map<String ,Object>> MapList= queryRunner.query(sql,new MapListHandler(),house.getCity(),house.getRoom(),house.getType());
                    Integer count =Integer.parseInt(MapList.get(0).get("total").toString());
                    return count;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            } else {
                sql = "select count(*) as total from t_house where city=?  and type =?";
                try {
                    List<Map<String ,Object>> MapList= queryRunner.query(sql,new MapListHandler(),house.getCity(),house.getType());
                    Integer count =Integer.parseInt(MapList.get(0).get("total").toString());
                    return count;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            }
        } else {
            if (house.getRoom() != null) {//城市空
                sql = "select count(*) as total from t_house where room=? and type =?";
                try {
                    List<Map<String ,Object>> MapList= queryRunner.query(sql,new MapListHandler(),house.getRoom(),house.getType());
                    Integer count =Integer.parseInt(MapList.get(0).get("total").toString());
                    return count;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            }
            return null;
        }
    }

    public void AddHouse(House house){//添加房子
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "insert into t_house (city,type,room,price,area,house_info,mannerId,mediatorId,showImg,time) values(?,?,?,?,?,?,?,?,?,?);";
        try {
            queryRunner.update(sql,house.getCity(),house.getType(),house.getRoom(),house.getPrice(),house.getArea(),house.getHouseInfo(),house.getMannerId(),house.getMediatorId(),house.getShowImg(),house.getTime());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void deleteById(Integer id){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "delete from t_house where id=?";
        try {
            queryRunner.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public House FindById(Integer id){//根据房子house id查找house
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,house_Info houseInfo from t_house where id = ?;";
        try {
           House house = queryRunner.query(sql,new BeanHandler<>(House.class),id);
           return house;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }



    public List<House>FindAll(Integer id , Pager pager){//根据中介id查找所有house
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,house_info as houseInfo from t_house where mediatorId=? limit ?,?";
        int start = (pager.getPageNO() - 1 ) * pager.getPageSize();
        try {
            List<House>list = queryRunner.query(sql,new BeanListHandler<>(House.class),id,start,pager.getPageSize());
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }



    public void UpDateHouse(House house){//根据id修改房子信息
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "update t_house set city=?,type=?,room=?,price= ?,area=?,house_info=?,mannerId=? , showImg=? , sold=? where id = ?;";
        try {
            queryRunner.update(sql,house.getCity(),house.getType(),house.getRoom(),house.getPrice(),house.getArea(),house.getHouseInfo(),house.getMannerId(),house.getShowImg(),house.getSold(),house.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }



    public List<House> FindAllInfo(Integer id) {
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,house_info AS houseInfo from  t_house where mannerId=?";
            try {
               List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), id);
                return houseList;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
        }

    //民宿分页，在HouseDao
    public List<House> findMinSuByPager(Pager pager)//搜索之后分页
    {
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());

        String sql =  "select * from t_house where type='民宿' limit ?,?";
        int start = (pager.getPageNO()-1)*pager.getPageSize();
        try {
            List<House> houses = queryRunner.query(sql,new BeanListHandler<>(House.class),start,pager.getPageSize());
            return houses;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

        public List<House> FindTypeMinsu(String type){//首页跳
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select * from t_house where type = ?;";

            try {
                List<House> houseList = queryRunner.query(sql,new BeanListHandler<>(House.class),type);
                return houseList;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
             return null;

        }

    public Integer getTotal(House house,Integer id) {//总页数
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql;
        if (house.getCity() != null) {
            if (house.getType() != null) {
                if (house.getRoom() != null) {
                    sql = "select count(*) as total from t_house where city=? and type = ? and room = ? and mannerId=?";
                    try {
                        List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getCity(), house.getType(), house.getRoom(), id);
                        Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                        return count;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                } else {
                    sql = "select count(*) as total from t_house where city=? and type = ? and mannerId=?";
                    try {
                        List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getCity(), house.getType(), id);
                        Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                        return count;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                }
            } else {
                if (house.getRoom() != null) {
                    sql = "select count(*) as total from t_house where city=? and room = ? and mannerId=?";
                    try {
                        List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getCity(), house.getRoom(), id);
                        Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                        return count;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                } else {
                    sql = "select count(*) as total from t_house where city=? and mannerId=?";
                    try {
                        List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getCity(), id);
                        Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                        return count;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                }
            }
        } else {
            if (house.getType() != null) {
                if (house.getRoom() != null) {
                    sql = "select count(*) as total from t_house where type = ? and room = ? and mannerId=?";
                    try {
                        List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getType(), house.getRoom(), id);
                        Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                        return count;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                } else {
                    sql = "select count(*) as total from t_house where type = ? and mannerId=?";
                    try {
                        List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getType(), id);
                        Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                        return count;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                }
            } else {
                sql = "select count(*) as total from t_house where room = ? and mannerId=?";
                try {
                    List<Map<String, Object>> MapList = queryRunner.query(sql, new MapListHandler(), house.getRoom(), id);
                    Integer count = Integer.parseInt(MapList.get(0).get("total").toString());
                    return count;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            }
        }
    }
    public List<House> FindPagerFall(Pager pager,Integer id,House house) {
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql;
        int start = (pager.getPageNO() - 1) * pager.getPageSize();
        if (house.getCity() != null) {
            if (house.getType() != null) {
                if (house.getRoom() != null) {
                    sql = "select * from t_house where city=? and type = ? and room = ? and mannerId=? order by id asc limit ?,?";
                    try {
                        List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getCity(), house.getType(), house.getRoom(), id, start, pager.getPageSize());
                        return houseList;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                } else {
                    sql = "select * from t_house where city=? and type = ?and mannerId=? order by id asc limit ?,?";
                    try {
                        List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getCity(), house.getType(), id, start, pager.getPageSize());
                        return houseList;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                }
            } else {
                if (house.getRoom() != null) {
                    sql = "select * from t_house where city=? and room = ? and mannerId=? order by id asc limit ?,?";
                    try {
                        List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getCity(), house.getRoom(), id, start, pager.getPageSize());
                        return houseList;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                } else {
                    sql = "select * from t_house where city=? and mannerId=? order by id asc limit ?,?";
                    try {
                        List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getCity(), id, start, pager.getPageSize());
                        return houseList;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                }
            }
        } else {
            if (house.getType() != null) {
                if (house.getRoom() != null) {
                    sql = "select * from t_house where type = ? and room = ? and mannerId=? order by id asc limit ?,?";
                    try {
                        List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getType(), house.getRoom(), id, start, pager);
                        return houseList;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                } else {
                    sql = "select * from t_house where type = ? and mannerId=? order by id asc limit ?,?";
                    try {
                        List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getType(), id, start, pager.getPageSize());
                        return houseList;
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    return null;
                }
            } else {
                sql = "select * from t_house where room = ? and mannerId=? order by id asc limit ?,?";
                try {
                    List<House> houseList = queryRunner.query(sql, new BeanListHandler<>(House.class), house.getRoom(), id, start, pager.getPageSize());
                    return houseList;
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return null;
            }
        }
    }
}


